﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMSX_register_tsx]
#-- Purpose:		Add jobs to a target server from the master server
#--	Last Update:	04/07/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMSX_register_tsx]
(
	@server_name				nvarchar(256),
	@deploy_backups_maint		bit				= NULL,
	@deploy_index_maint			bit				= NULL
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE @next_msx nvarchar(256), @job_name nvarchar(256), @sError nvarchar(2000), @changes_made bit,
		@action_backup nvarchar(256), @action_index nvarchar(256), @action_other nvarchar(256)

--- Determine the next MSX group to assign
SELECT	@next_msx = [dbo].[fnMSX_get_next_msx_group](@server_name),
		@changes_made = 0

--- If @deploy_backups_maint or @deploy_index_maint were not passed in, lookup there values from the catalog
IF @deploy_backups_maint IS NULL
	SELECT		@deploy_backups_maint = [ssd_deploy_msx_backup]
	FROM		[dbo].[tblMSX_server_discovery]
	WHERE		[ssd_server_name] = @server_name

IF @deploy_index_maint IS NULL
	SELECT		@deploy_index_maint = [ssd_deploy_msx_index]
	FROM		[dbo].[tblMSX_server_discovery]
	WHERE		[ssd_server_name] = @server_name

--- Determine if this server has been registered with MSX
IF NOT EXISTS ( SELECT TOP 1 * FROM [msdb].[dbo].[systargetservers] WHERE server_name = @server_name)
  BEGIN
	SELECT	@sError = N'The server (' + @server_name + N') has not been enlisted with this MSX server.'
	RAISERROR (@sError, 16, 0)
	RETURN -999
  END

--- Determine if this server has been assigned to an MSX group
IF NOT EXISTS 
	(
		SELECT		TOP 1 srv.name
		FROM		[msdb].[dbo].[systargetservergroups] srv
		JOIN		[msdb].[dbo].[systargetservergroupmembers] mbr ON srv.servergroup_id = mbr.servergroup_id
		JOIN		[msdb].[dbo].[systargetservers] s ON mbr.server_id = s.server_id
		WHERE		REPLACE(srv.name, 'MSX ', '') BETWEEN 1 AND 6
					AND s.server_name = @server_name
		GROUP BY	srv.name,
					s.server_name
	)
  BEGIN
	--- Assign the server to the group
	EXEC	[msdb].[dbo].[sp_add_targetsvrgrp_member]		@group_name = @next_msx, 
															@server_name = @server_name
	SET		@changes_made = 1
  END

--- Assign any jobs that have not already been assigned
DECLARE crs CURSOR FOR
	SELECT		DISTINCT
				j.name,
				(CASE	WHEN j.name LIKE '%Backup%'
								AND j.name NOT LIKE '%Prune%'
								AND js2.job_id IS NOT NULL
								AND @deploy_backups_maint = 0
								THEN 'remove'
						WHEN j.name LIKE '%Backup%'
								AND j.name NOT LIKE '%Prune%'
								AND js2.job_id IS NULL
								AND @deploy_backups_maint = 1
								THEN 'add'
						ELSE 'ignore'
						END) action_backup,
				(CASE	WHEN j.name LIKE '%Index%'
							AND js2.job_id IS NOT NULL
							AND @deploy_index_maint = 0
							THEN 'remove'
						WHEN j.name LIKE '%Index%'
							AND js2.job_id IS NULL
							AND @deploy_index_maint = 1
							THEN 'add'
						ELSE 'ignore'
						END) action_index,
				(CASE	WHEN NOT (
									(j.name LIKE '%Backup%' AND j.name NOT LIKE '%Prune%')
									OR j.name LIKE '%Index%'
								)
								AND js2.job_id IS NULL
								THEN 'add'
						ELSE 'ignore'
						END) action_other
	FROM		[msdb].[dbo].[sysjobs] j
	JOIN		[msdb].[dbo].[syscategories] c ON j.category_id = c.category_id
				AND c.category_type = 2
	LEFT JOIN	[msdb].[dbo].[sysjobservers] js2 ON j.job_id = js2.job_id
				AND js2.server_id IN (SELECT server_id FROM [msdb].[dbo].[systargetservers] WHERE server_name = @server_name)
	WHERE		j.enabled = 1
				AND (
					'MSX ' + CAST(ISNULL(LTRIM(RTRIM(PARSENAME(REPLACE(j.name, 'MSX', '.'), 1))), 0) as varchar(5)) 
						IN (
							SELECT		srv.name
							FROM		[msdb].[dbo].[systargetservergroups] srv
							JOIN		[msdb].[dbo].[systargetservergroupmembers] mbr ON srv.servergroup_id = mbr.servergroup_id
							JOIN		[msdb].[dbo].[systargetservers] s ON mbr.server_id = s.server_id
										AND s.server_name = @server_name
						)
					OR j.name LIKE N'% - MSX'
				)
	ORDER BY	j.name
OPEN crs
FETCH NEXT FROM crs INTO @job_name, @action_backup, @action_index, @action_other
WHILE @@FETCH_STATUS <> -1
  BEGIN
	--- Add the job, since by configuration settings it belongs.
	IF 'add' IN (@action_backup, @action_index, @action_other)
	  BEGIN
		EXEC	[msdb].[dbo].[sp_add_jobserver] @job_name = @job_name, @server_name = @server_name
		SET		@changes_made = 1
	  END
	
	--- Remove the job, since by configuration settings it doesn't belong.
	IF 'remove' IN (@action_backup, @action_index, @action_other)
	  BEGIN
		EXEC	[msdb].[dbo].[sp_delete_jobserver] @job_name = @job_name, @server_name = @server_name
		SET		@changes_made = 1
	  END

	FETCH NEXT FROM crs INTO @job_name, @action_backup, @action_index, @action_other
  END
CLOSE crs
DEALLOCATE crs

SELECT CAST(@changes_made as int) changes_made

SET NOCOUNT OFF